/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package quanlinganhang.dao;

import java.sql.CallableStatement;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import javax.swing.table.DefaultTableModel;
import quanlinganhang.dto.AccountDTO;

/**
 *
 * @author phamduong
 */
public class AccountDAO {

    /**
     * Delete account
     *
     * @param accountNo
     * @return
     */
    public static boolean DeleteAccount(String accountNo)
            throws ClassNotFoundException, SQLException {
        List<Parameter> sqlParams = new ArrayList<>();
        sqlParams.add(new Parameter("SoTaiKhoan", accountNo));
        return (SqlDAOHelper.ExecuteNonQuery("Delete_TaiKhoan", sqlParams) > 0);
    }

    //Đóng tài khoản
    public static boolean CloseAccount(String accountNo)
            throws ClassNotFoundException, SQLException {
        List<Parameter> sqlParams = new ArrayList<>();
        sqlParams.add(new Parameter("SoTaiKhoan", accountNo));
        return (SqlDAOHelper.ExecuteNonQuery("Close_TaiKhoan", sqlParams) > 0);
    }

    /**
     *
     *
     * @return @throws SQLException
     * @throws ClassNotFoundException
     */
    public static ArrayList GetListAccount()
            throws SQLException, ClassNotFoundException {
        return SqlDAOHelper.ExecuteQuery("DsTaiKhoan", null);
    }

    public static ArrayList GetListRemoveAccount()
            throws SQLException, ClassNotFoundException {
        return SqlDAOHelper.ExecuteQuery("Select_RemoveAcc", null);
    }

    public static AccountDTO GetAccountFormId(String accountNo)
            throws SQLException, ClassNotFoundException, ParseException {
        List<Parameter> sqlParams = new ArrayList<>();
        sqlParams.add(new Parameter("SoTaiKhoan", accountNo));
        DefaultTableModel table = SqlDAOHelper.ExecuteQueryReturnTable(
                "Get_Account", sqlParams);
        AccountDTO account = new AccountDTO();
        account.setID(Long.parseLong(table.getValueAt(0, 0).toString()));
        account.setSoTaiKhoan(accountNo);
        account.setPinCode(table.getValueAt(0, 2).toString());
        account.setHoTen(table.getValueAt(0, 3).toString());
        account.setSoCMND(table.getValueAt(0, 4).toString());
        account.setDiaChi(table.getValueAt(0, 5).toString());
        account.setDienThoai(table.getValueAt(0, 6).toString());
        account.setEmail(table.getValueAt(0, 7).toString());

        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-mm-dd");

        Date openDate = dateFormat.parse(table.getValueAt(0, 8).toString());
        account.setNgayMo(openDate);

        Date closeDate = dateFormat.parse(table.getValueAt(0, 9).toString());
        account.setNgayDaoHan(closeDate);

        account.setMaLoaiTK(Long.parseLong(table.getValueAt(0, 10).toString()));
        String odd = table.getValueAt(0, 11).toString();
        odd = odd.substring(0, odd.indexOf('.'));
        account.setSoDuTK(Long.parseLong(odd));
        account.setDaDong(Boolean.parseBoolean(table.getValueAt(0, 12).toString()));
        account.setGhiChu(table.getValueAt(0, 13).toString());
        return account;
    }

    /**
     * Get account's number
     *
     * @param ID
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public static long getListAccountNo(String ID)
            throws SQLException, ClassNotFoundException {
        //return SqlDAOHelper.ExecuteQuery("Select_SoTK", null);
        CallableStatement cs = SqlDAOHelper.connection.prepareCall("{call Select_SoTK (?)}");
        cs.registerOutParameter("ID", java.sql.Types.NVARCHAR);
        cs.executeUpdate();
        long check = cs.getLong("ID");
        SqlDAOHelper.CloseConnection();
        return (check + 1);
    }

    /**
     * Get list account number
     *
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public static ArrayList GetAccountType()
            throws SQLException, ClassNotFoundException {
        return SqlDAOHelper.ExecuteQuery("GetAccountTypes", null);
    }

    /**
     * Perform search
     *
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public static ArrayList Search(String accountno)
            throws SQLException, ClassNotFoundException {
        List<Parameter> sqlParams = new ArrayList<>();
        sqlParams.add(new Parameter("SoTaiKhoan", accountno));
        return SqlDAOHelper.ExecuteQuery("DsTaiKhoan", sqlParams);
    }

    /**
     * Update account's infomation
     *
     * @param account
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static boolean UpdateAccount(AccountDTO account)
            throws ClassNotFoundException, SQLException {
        List<Parameter> sqlParams = new ArrayList<>();
        sqlParams.add(new Parameter("SoTaiKhoan", account.getSoTaiKhoan()));
        sqlParams.add(new Parameter("HoTen", account.getHoTen()));
        sqlParams.add(new Parameter("SoCMND", account.getSoCMND()));
        sqlParams.add(new Parameter("DiaChi", account.getSoTaiKhoan()));
        sqlParams.add(new Parameter("DienThoai", account.getDienThoai()));
        sqlParams.add(new Parameter("Email", account.getEmail()));
        sqlParams.add(new Parameter("NgayMo", account.getNgayMo()));
        sqlParams.add(new Parameter("NgayDaoHan", account.getNgayDaoHan()));
        sqlParams.add(new Parameter("MaLoaiTK", account.getMaLoaiTK()));
        sqlParams.add(new Parameter("SoDuTK", account.getSoDuTK()));
        sqlParams.add(new Parameter("DaDong", account.isDaDong()));
        sqlParams.add(new Parameter("GhiChu", account.getGhiChu()));
//        sqlParams.add(new Parameter("GhiChu", account.getGhiChu()));
//        sqlParams.add(new Parameter("GhiChu", account.getGhiChu()));

        return (SqlDAOHelper.ExecuteNonQuery("UpdateTaiKhoan", sqlParams) > 0);
    }

    /**
     * Add account
     *
     * @param account
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static boolean AddAcount(AccountDTO account)
            throws ClassNotFoundException, SQLException {
        List<Parameter> sqlParams = new ArrayList<>();
        sqlParams.add(new Parameter("SoTaiKhoan", account.getSoTaiKhoan()));
        sqlParams.add(new Parameter("HoTen", account.getHoTen()));
        sqlParams.add(new Parameter("SoCMND", account.getSoCMND()));
        sqlParams.add(new Parameter("DiaChi", account.getSoTaiKhoan()));
        sqlParams.add(new Parameter("DienThoai", account.getDienThoai()));
        sqlParams.add(new Parameter("Email", account.getEmail()));
        Timestamp ngayMo = getTimestampFromDate(account.getNgayMo());
        Timestamp ngayDong = getTimestampFromDate(account.getNgayDaoHan());
        sqlParams.add(new Parameter("NgayMo", ngayMo));
        sqlParams.add(new Parameter("NgayDaoHan", ngayDong));
        sqlParams.add(new Parameter("MaLoaiTK", account.getMaLoaiTK()));
        sqlParams.add(new Parameter("SoDuTK", account.getSoDuTK()));
        sqlParams.add(new Parameter("DaDong", account.isDaDong()));
        sqlParams.add(new Parameter("GhiChu", account.getGhiChu()));
        sqlParams.add(new Parameter("PinCode", account.getPinCode()));
        sqlParams.add(new Parameter("ID", account.getID()));

        return (SqlDAOHelper.ExecuteNonQuery("Insert_TaiKhoan", sqlParams) > 0);
    }

    public static Timestamp getTimestampFromDate(Date date) {
        java.sql.Timestamp timeStamp = null;
        try {
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(new Date());
            Date dt = new java.sql.Date(calendar.getTimeInMillis());
            Time sqlTime = new java.sql.Time(calendar.getTime().getTime());
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
            date = simpleDateFormat.parse(dt.toString() + " " + sqlTime.toString());
            timeStamp = new java.sql.Timestamp(date.getTime());
        } catch (ParseException pe) {
        } catch (Exception e) {
        }
        return timeStamp;
    }

    /**
     *
     * database
     *
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static long GetCurrentAccountNo()
            throws ClassNotFoundException, SQLException {
        SqlDAOHelper.OpenConnection();
        CallableStatement cs = SqlDAOHelper.connection.prepareCall("{call Select_AccCurrent (?)}");
        cs.registerOutParameter("SoTaiKhoan", java.sql.Types.NVARCHAR);
        cs.executeUpdate();
        long check = cs.getLong("SoTaiKhoan");
        SqlDAOHelper.CloseConnection();
        return (check + 1);
    }

    /**
     *
     *
     * @param tenLoaiTK
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static int GetAccountTerm(String tenLoaiTK)
            throws ClassNotFoundException, SQLException {
        SqlDAOHelper.OpenConnection();
        CallableStatement cs = SqlDAOHelper.connection.prepareCall("{call Select_KyHan (?, ?)}");
        cs.setObject("TenLoaiTK", tenLoaiTK);
        cs.registerOutParameter("KyHan", java.sql.Types.NVARCHAR);
        cs.executeUpdate();
        int term = cs.getInt("KyHan");
        SqlDAOHelper.CloseConnection();
        return term;
    }

    /**
     *
     *
     * @param tenLoaiTK
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static long GetAccountTypeId(String tenLoaiTK)
            throws ClassNotFoundException, SQLException {
        SqlDAOHelper.OpenConnection();
        CallableStatement cs = SqlDAOHelper.connection.prepareCall("{call GetAccountTypeId (?, ?)}");
        cs.setObject("TenLoaiTK", tenLoaiTK);
        cs.registerOutParameter("MaLoaiTK", java.sql.Types.NVARCHAR);
        cs.executeUpdate();
        long id = cs.getLong("MaLoaiTK");
        SqlDAOHelper.CloseConnection();
        return id;
    }
}
